﻿Imports System.Data.OleDb
Imports System.Net.Mail
Public Class Alleaandeelhouders
    Inherits System.Web.UI.Page
    Dim strgekozenVergadering As String
    Dim strCode() As String
    Dim intDbHoeveelAandeelhouders As Integer
    Dim stremailadres() As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        '   If IsPostBack = False Then


        '       Dim cnndb3 As New OleDbConnection
        '       Dim cmdCommando As New OleDbCommand
        '       Dim dtrdatareader As OleDbDataReader
        '       Dim strsqlEmailadress As String
        '       Dim strsqlVergaderingen As String
        '       strsqlEmailadress = "SELECT COUNT(id)FROM [tblemailadres]"
        '       cnndb3.ConnectionString = My.Settings.csAandeelhouders
        '       cmdCommando.Connection = cnndb3
        '       cmdCommando.CommandText = strsqlEmailadress
        '       cnndb3.Open()
        '       intDbHoeveelAandeelhouders = cmdCommando.ExecuteScalar()
        '       cnndb3.Close()
        '       strsqlVergaderingen = "SELECT [Vergadering] FROM [tblVergadering]"
        '       cmdCommando.CommandText = strsqlVergaderingen
        '       cnndb3.Open()
        '       dtrdatareader = cmdCommando.ExecuteReader
        '       Do While dtrdatareader.Read()
        '           dpdVergadering.Items.Add(dtrdatareader.Item("Vergadering"))
        '       Loop
        '       cnndb3.Close()
        '       myGegevens()
        '   End If
    End Sub
    Public Function myReplace() As Boolean
        'DECLARATIES
        'Datums en plaats instellen
        Static strDatum1 As String = "[$date1$]"
        Static strDatum2 As String = "[$date2$]"
        Static strPlaats As String = "[$plaats$]"

        Dim strCode As String
        Dim strDatum1Db As String
        Dim strDatum2Db As String
        Dim strPlaatsDb As String
        Dim cnndb3 As New OleDbConnection
        Dim cmdCommando As New OleDbCommand
        Dim dtrDataReader2 As OleDbDataReader
        Dim strsqlEmailadress As String = "SELECT [Datum], [Start datum], [Plaats] FROM [tblVergadering] WHERE [Vergadering] ='" & strgekozenVergadering & "'"

        'Invoer + verwerking
        strgekozenVergadering = dpdVergadering.Text

        'Voorbereiding connectie
        cnndb3.ConnectionString = My.Settings.csAandeelhouders
        cmdCommando.Connection = cnndb3
        cmdCommando.CommandText = strsqlEmailadress

        'Connectie openen
        cnndb3.Open()
        dtrDataReader2 = cmdCommando.ExecuteReader
        Do While dtrDataReader2.Read()
            strDatum1Db = dtrDataReader2.Item("Datum")
            strDatum2Db = dtrDataReader2.Item("Start datum")
            strPlaatsDb = dtrDataReader2.Item("Plaats")

        Loop

        cnndb3.Close()
        If IsNothing(dtrDataReader2) Then
            dtrDataReader2.Close()
        End If


        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDatum1, strDatum1Db)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDatum2, strDatum2Db)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strPlaats, strPlaatsDb)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(".uniqu.", "<a href=AanofAfwezig.aspx?id=" & strCode(0) & ">Klik hier voor aanwezig of afwezig</a>")

        strDatum1 = strDatum1Db
        strDatum2 = strDatum2Db
        strPlaats = strPlaatsDb
        btnVersturen.Enabled = True
        Return True

    End Function

    Protected Sub btnVersturen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnVersturen.Click
        'declareer variabele
        myReplace()
        Dim objMail As New MailMessage()
        Dim objMijnAdres As New MailAddress("Proventi@hotmail.be", "Proventi")
        Dim objSmtp As New SmtpClient(My.Settings.SMTPServer)
        Dim strAan As String
        Dim strHyperlinkOude As String
        Dim strhyperlinkNieuwe As String
        For intTeller As Integer = 0 To intDbHoeveelAandeelhouders - 1
            strAan = stremailadres(intTeller)
            If intTeller = 0 Then
                'nothing
            Else
                strHyperlinkOude = "<a href=AanofAfwezig.aspx?id=" & strCode(intTeller) & ">Klik hier voor aanwezig of afwezig</a>"
                strhyperlinkNieuwe = "<a href=AanofAfwezig.aspx?id=" & strCode(intTeller + 1) & ">Klik hier voor aanwezig of afwezig</a>"
            End If
            'stel de verzender en ontvanger in 
            objMail.From = objMijnAdres
            objMail.To.Add(strAan)
            'stel het subject en de tekst in 
            objMail.Subject = txtOnderwerp.Text
            mailContent.InnerHtml = mailContent.InnerHtml.Replace(strHyperlinkOude, strhyperlinkNieuwe)
            objMail.Body = mailContent.InnerText
            objMail.IsBodyHtml = True
            'verzend het bericht 
            objSmtp.Send(objMail)

        Next
        lblMailVerstuurd.Text = "De mailtjes zijn verstuurd"
    End Sub

    Protected Sub btnMenu_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnMenu.Click
        Server.Transfer("default.aspx")
    End Sub

    Protected Sub btnVoorbeeld_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnVoorbeeld.Click
        strgekozenVergadering = dpdVergadering.Text
        myReplace()

    End Sub
    Public Function myGegevens() As Boolean
        Dim cnndb3 As New OleDbConnection
        Dim cmdCommando As New OleDbCommand
        Dim dtrDataReader As OleDbDataReader
        Dim strsqlEmailadress As String
        Dim intTeller As String = -1
        strsqlEmailadress = "SELECT [E-mail adress],[Code] FROM [tblemailadres]"
        cnndb3.ConnectionString = My.Settings.csAandeelhouders
        cmdCommando.Connection = cnndb3
        cmdCommando.CommandText = strsqlEmailadress


        cnndb3.Open()
        dtrDataReader = cmdCommando.ExecuteReader
        Do While dtrDataReader.Read()
            intTeller += 1
            ReDim Preserve stremailadres(intDbHoeveelAandeelhouders)
            ReDim Preserve strCode(intDbHoeveelAandeelhouders)
            stremailadres(intTeller) = dtrDataReader.Item("E-mail adress")
            strCode(intTeller) = dtrDataReader.Item("Code")

        Loop
        cnndb3.Close()
        If IsNothing(dtrDataReader) Then
            dtrDataReader.Close()
        End If


    End Function
End Class